package com.aischool.service;

import java.util.ArrayList;
import java.util.List;

import com.aischool.params.AiParentParams;
import com.aischool.utils.RegexUtil;
import com.aischool.utils.StateEnum;
import com.aischool.utils.StringUtil;
import com.jfinal.aop.Inject;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.ehcache.CacheKit;

public class AiParentService {

	public final String CK_CacheName = "CacheParents";
	public final String CK_AiParent_UID_Pre = "CK_AiParent_UID_";
	
	@Inject 
	SysUserService sysUserService;
	
	/**
	 * 获取分页数据
	 * @param params
	 * @return
	 */
	public Page<Record> getPage(AiParentParams params){
		String sql_select = "SELECT a.*, b.student_name, b.student_sno, c.class_name, e.kindred_name  ";
		String sql_from   =	"FROM ai_parent a \r\n" + 
				"LEFT JOIN ai_student b ON b.student_id = a.student_id \r\n" + 
				"LEFT JOIN ai_class c ON c.class_id = b.class_id \r\n" + 
				"LEFT JOIN ai_kindred e ON e.kindred_id = a.kindred_id ";
		String sql_where  =	"WHERE a.parent_state >= 0 ";
		String sql_order = "ORDER BY a.parent_id DESC ";
		
		//System.err.println(JSONObject.toJSONString(params));
		
		List<Object> listObj = new ArrayList<Object>();
		if (params.getState() != StateEnum.ALL.getState()) {
			sql_where =	"WHERE a.parent_state = ?";
			listObj.add(params.getState());
		}
		
		if (params.getOrgId()>0) {
			sql_where += " AND a.org_id = ? ";
			listObj.add(params.getOrgId());
		}
		
		if (StrKit.notBlank(params.getKeywords())) {
			sql_where += " AND (a.parent_name || a.parent_mobile) LIKE ? ";
			listObj.add("%" + params.getKeywords() + "%");
		}
		return Db.paginate(params.getPageNo(), params.getPageSize(), sql_select, sql_from + sql_where + sql_order, listObj.toArray());
	}
	
	public List<Record> getParentsByClassId(int class_id){
		return Db.find("SELECT a.*, b.user_img " + 
				"FROM ai_parent a " + 
				"INNER JOIN sys_user b ON b.user_id = a.user_id "
				+ " WHERE a.parent_state = 1 AND a.class_id = ?", class_id);
	}
	
	public List<Record> getParents(int student_id){
		return Db.find("SELECT a.*, b.user_img, c.student_name, e.kindred_name " + 
				"FROM ai_parent a " + 
				"INNER JOIN sys_user b ON b.user_id = a.user_id "
				+ "LEFT JOIN ai_student c ON c.student_id = a.student_id "
				+ "INNER JOIN ai_kindred e ON e.kindred_id = a.kindred_id "
				+ " WHERE a.parent_state = 1 AND a.student_id = ?", student_id);
	}
	
	public Record getOne(int parent_id) {
		return Db.findFirst("SELECT a.* "
				+ "FROM ai_parent a "
				+ " WHERE a.parent_id = ?", parent_id);
	}
	
	public Record getOneByUserId(int user_id) {
		return Db.findFirstByCache(CK_CacheName, CK_AiParent_UID_Pre + user_id,
				"SELECT a.*, b.student_name, b.student_sno, c.org_name, d.class_name, e.kindred_name "
				+ "FROM ai_parent a LEFT JOIN ai_student b ON b.student_id = a.student_id "
				+ "INNER JOIN ai_org c ON c.org_id = a.org_id "
				+ "LEFT JOIN ai_class d ON d.class_id = a.class_id  \r\n" + 
				"INNER JOIN ai_kindred e ON e.kindred_id = a.kindred_id "
				+ "WHERE a.user_id = ?", user_id);
	}
	
	public int updateClassid(int student_id, int class_id) {
		int ir = Db.update("UPDATE ai_parent SET class_id = ? WHERE student_id = ?",
				class_id, student_id);
		if (ir > 0) {
			removeCacheByStudentId(student_id);
		}
		return ir;
	}
	
	public boolean del(int parent_id) {
		Record rd = getOne(parent_id);
		if (rd != null) {
			int ir = Db.delete("DELETE FROM ai_parent WHERE parent_id = ?", parent_id);
			if (ir > 0) {
				sysUserService.delUser(rd.getInt("user_id"));
				removeCacheByUserId(rd.getInt("user_id"));
			}
			return ir > 0;
		}
		return false;
	}
	
	public boolean delForOrg(int org_id, int parent_id) {
		Record rd = getOne(parent_id);
		if (rd != null) {
			int ir = Db.delete("DELETE FROM ai_parent WHERE org_id = ? AND parent_id = ?", org_id, parent_id);
			if (ir > 0) {
				sysUserService.delUser(rd.getInt("user_id"));
				
				removeCacheByUserId(rd.getInt("user_id"));
			}
			
			return ir > 0;
		}
		return false;
	}
	
	public boolean delMore(String ids) {
		if (StrKit.isBlank(ids)) {
			return false;
		}
		if (RegexUtil.isSqlIds(ids)) {
			List<Record> listrd = Db.find("SELECT a.user_id FROM ai_parent a INNER JOIN (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt ON a.parent_id = CAST(tt.ids AS INTEGER)", StringUtil.trim(ids, ','));
			String uids = "";
			for(Record rdu : listrd) {
				uids += rdu.getInt("user_id") + ",";
			}
			int ir = Db.delete("DELETE FROM ai_parent a " + 
					"USING (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " +
					"WHERE a.parent_id = CAST(tt.ids AS INTEGER)", 
					StringUtil.trim(ids, ','));
			if (ir > 0 && uids.length()>0) {
				sysUserService.delMore(StringUtil.trim(uids, ','));
			}
			if (ir>0) {
				removeCacheByUserIds(uids);
			}
			return ir > 0;
			
		}
		
		return false;
	}
	
	public boolean delMoreForOrg(int org_id, String ids) {
		if (StrKit.isBlank(ids)) {
			return false;
		}
		if (RegexUtil.isSqlIds(ids)) {
			
			List<Record> listrd = Db.find("SELECT a.user_id FROM ai_parent a INNER JOIN (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt ON a.parent_id = CAST(tt.ids AS INTEGER)", StringUtil.trim(ids, ','));
			String uids = "";
			for(Record rdu : listrd) {
				uids += rdu.getInt("user_id") + ",";
			}
			int ir = Db.delete("DELETE FROM ai_parent a " + 
					"USING (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " +
					"WHERE a.org_id = ? AND a.parent_id = CAST(tt.ids AS INTEGER)", 
					StringUtil.trim(ids, ','), org_id);
			if (ir > 0 && uids.length()>0) {
				sysUserService.delMore(StringUtil.trim(uids, ','));
			}
			if (ir>0) {
				removeCacheByUserIds(uids);
			}
			return ir > 0;
			
		}
		
		return false;
	}
	

	public void removeCacheByUserIds(String user_ids) {
		String[] array = StringUtil.trim(user_ids, ',').split(",");
		if (array!= null && array.length>0) {
			for(int i=0; i<array.length; i++) {
				removeCacheByUserId(Integer.parseInt(array[i]));
			}
		}
	}
	
	public void removeCacheByStudentId(int student_id) {
		List<Record> list = this.getParents(student_id);
		if (list != null) {
			for(int i=0; i<list.size(); i++) {
				removeCacheByUserId(list.get(i).getInt("user_id"));
			}
		}
	}
	
	public void removeCacheByClassId(int class_id) {
		List<Record> list = getParentsByClassId(class_id);
		if (list != null) {
			for(int i=0; i<list.size(); i++) {
				removeCacheByUserId(list.get(i).getInt("user_id"));
			}
		}
	}
	
	public void removeCacheByUserId(int user_id) {
		CacheKit.remove(CK_CacheName, CK_AiParent_UID_Pre + user_id); 
	}

	public void removeCache() {
		CacheKit.removeAll(CK_CacheName);
	}
}
